Structured Query Language (SQL) is the ANSI standard language for interacting with databases. SQL allows users to combine information from many different sets of records (tables) in a database in order to retrieve data from the database. However, the power of SQL has some drawbacks. For example, a user can combine different tables in an SQL query, which may create queries that can be impossible for the database engine to resolve or that may utilize a significant amount of system resources.
Database systems often include performance tuning systems or methods. For example, Oracle 7 provides various scripts (e.g., UTILBSTAT and UTLESTAT) that can be used to assist in performance tuning by summarizing the operating state of a database in one or more reports. These scripts are a set of SQL scripts useful for capturing a snapshot of system-wide database performance statistics and generating a report which can help an operator optimize the performance of the database. The database operator can use the reports for fine-tuning the database performance and for preventative maintenance of the database. A report may include, for example, information about database memory objects, including library cache, dictionary cache, latch usage, file I/O and rollback statistics.
A problem with existing systems is that they often limit the amount of time it takes to produce an optimal execution plan. In addition, a knowledgeable user might have more insight into the data than represented by the object statistics, if object statistics are present at all. Existing systems often do not take advantage of the knowledge of the user. In addition, although statistics may be useful when properly provided and analyzed, statistics might not exist at all if the objects have not yet been analyzed, or the statistics may be out-of-date. A specific statistic that would provide insight to the distribution of the data may not be present in a particular instance. On the other hand, the user may have first hand knowledge that may be useful in tuning database queries that my not otherwise be available. For example, the user may know that the query will be executed at a time when more resources, such as multiple CPUs, will be available, even though the system is normally fully utilized. Such information could prove very helpful during a database query tuning process.